package DBLayer;

import java.util.ArrayList;

import ModelLayer.City;
import ModelLayer.Customer;
import java.sql.*;

public class DBCustomer implements IFDBCustomer {
	private Connection con = DBConnection.getInstance().getDBcon();
	private DBCity cityDB = new DBCity();
	//Public methods
	
	@Override
	public ArrayList<Customer> getAllCustomers(boolean retriveAssociation) {
		return miscWhere("", retriveAssociation);
	}

	@Override
	public Customer getCustomerByPhoneNo(int _phoneNo, boolean retriveAssociation) {
		String wClause = "phoneNo = '" + _phoneNo + "'";
		System.out.println("Search Customer " + wClause);
		return singleWhere(wClause, retriveAssociation);
	}

	@Override
	public Customer getCustomerByName(String _name, boolean retriveAssociation) {
		String wClause = "name = '" + _name + "'";
		System.out.println("Search Customer " + wClause);
		return singleWhere(wClause, retriveAssociation);
	}

	@Override
	public int insertCustomer(Customer _cust)  {

		int rc = -1;
		String query="INSERT INTO Customer(name, address, codezip, phoneno, email, cprcvr)  VALUES('"+
				_cust.getName() + "','"  +
				_cust.getAddress()  + "','"  +
				_cust.getCodeZip()  + "','"  +
				_cust.getPhoneNo()  + "','"  +
				_cust.getEmail()  + "','"  +
				_cust.getCprCvr()  + "')";

		System.out.println("insert : " + query);
		try{ // insert new customer
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try
		catch(SQLException ex){
			System.out.println("Customer was inserted");
		}
		return(rc);
	}

	@Override
	public int updateCustomer(Customer customer) {
		Customer custObj  = customer;
		int rc=-1;

		String query="UPDATE customer SET "+
				"name ='"+ custObj.getName()+"', "+
				"address ='"+ custObj.getAddress() + "', " +
				"codezip ='"+ custObj.getCodeZip() + "', " +
				"email ='"+ custObj.getEmail() + "', " +
				"cprcvr ='"+ custObj.getCprCvr() + "' " +
				" WHERE phoneno = '"+ custObj.getPhoneNo() + "'";
		System.out.println("Update query:" + query);
		try{ // update employee
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}//slut try
		catch(Exception ex){
			System.out.println("Update exception in customer db: "+ex);
		}
		return(rc);
	}

	@Override
	public int deleteCustomer(int phoneNo) {
		int rc=-1;

		String query="DELETE FROM customer WHERE phoneno = '" +
				phoneNo + "'";
		System.out.println(query);
		try{ // delete from customer
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();  
		}//slut try	
		catch(Exception ex){
			System.out.println("Delete exception in customer db: "+ex);
		}
		return(rc);
	}

	//private methods

	//michWere is used whenever we want to select more than one customer
	private ArrayList<Customer> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Customer> list = new ArrayList<Customer>();	
		String query =  buildQuery(wClause);

		try{ // read the PrivateCustomer from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while( results.next() )
			{
				Customer custObj = new Customer();
				custObj = buildCustomer(results);				
				list.add(custObj);		
			}//end while
			stmt.close();     

		}//end try	
		catch(Exception e){
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}

		return list;
	}

	//Singlewhere to select only one Customer 	
	private Customer singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Customer custObj = new Customer();
		
	
			String query =  buildQuery(wClause);
			System.out.println(query);
			try{ // read the customer from the database
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				results = stmt.executeQuery(query);

				if( results.next() ){
					custObj = buildCustomer(results);
					stmt.close();
				}		
				else{ //no customer was found
					custObj = null;
				}
			}
			catch(Exception e){
				System.out.println("Query exception: "+e);
			}

		return custObj;
	}

	//method to build the query for Customer
	private String buildQuery(String wClause)
	{
		String query="SELECT name, address,codeZip, phoneNo, email, cprCvr FROM customer";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}

	//method to build a Customer object
	private Customer buildCustomer(ResultSet results)
	{   
		Customer custObj = new Customer();
		try{ // the columns from the table customer  are used
			custObj.setName(results.getString("name"));
			custObj.setAddress(results.getString("address"));
			custObj.setCodeZip(results.getInt("codezip"));
			custObj.setCity(cityDB.getCityByCodeZip(custObj.getCodeZip(), false).getCity());
			custObj.setPhoneNo(results.getInt("phoneno"));
			custObj.setEmail(results.getString("email"));
			custObj.setCprCvr(results.getString("cprCvr"));;
		}
		catch(Exception e)
		{
			System.out.println("error in building the customer object");
		}
		return custObj;
	}
}

